package com.wy.component.office;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

public class POIExcel {

	private static final String PATH = "Path";
	private static final String SHEET = "Sheet";
	private static final String DATA = "Data";
	private static final String COL = "Col";
	private static final String ROW = "Row";
	private static final String TAG = "Tag";
	private static final String TYPE = "Type";
	private static final String RULE = "Rule";
	private static final String RESTRICT = "Restrict";

	private static final String ADD = "+";
	private static final String DOT = ",";
	private static final String DATE = "Date";
	private static final String CONDATION = "Condation";
	
	private static Map ruleMap = new HashMap();
	
	private static SimpleDateFormat sysDateFormat = new SimpleDateFormat("yyyy/MM/dd");
	
	/**
	 * 获得日期
	 * @return
	 */
	public static String getCurrentDate() {
		return sysDateFormat.format(new Date());
	}
	/**
	 * 判断闰年
	 * @param year
	 * @return
	 */
	public static boolean isRunNian(String year) {
		int y = Integer.parseInt(year);
		if (y % 4 == 0 && y % 100 != 0 || y % 400 == 0) {
			return true;
		}
		return false;
	}
	
	/**
	 * 解析xml配置文件
	 * 
	 * @param fileName
	 * @return
	 * @throws FileNotFoundException
	 * @throws DocumentException
	 */
	public static List analyXml(String fileName) throws FileNotFoundException,
			DocumentException {
		SAXReader reader = new SAXReader();
		Document doc = null;
		doc = reader.read(new FileInputStream(new File(fileName)));
		Element root = doc.getRootElement();
		List list = new ArrayList();
		String path = doc.getRootElement().elementText(PATH);
		list.add(path);
		String sheetNum = doc.getRootElement().elementText(SHEET);
		list.add(sheetNum);
		List<Element> dataList = root.elements(DATA);
		for (Element dataNode : dataList) {
			String col = dataNode.elementText(COL);
			String row = dataNode.elementText(ROW);
			String tag = dataNode.elementText(TAG);
			String type = dataNode.elementText(TYPE);
			String rule = dataNode.elementText(RULE);
			String restrict = dataNode.elementText(RESTRICT);
			/*System.out.println(col + " " + row + " " + tag + " " + type + " "
					+ rule + " " + restrict);*/
			List tmp = new ArrayList();
			tmp.add(col);
			tmp.add(row);
			tmp.add(tag);
			tmp.add(type);
			tmp.add(rule);
			tmp.add(restrict);
			list.add(tmp);
		}
		return list;
	}
	
	/**
	 * 获取模板
	 * @param list
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static HSSFWorkbook getBook(List list, String path) throws FileNotFoundException, IOException {
		// 先读取模板 
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(path + list.get(0).toString())); 
        // 基于模板创建workbook 
        HSSFWorkbook workbook = new HSSFWorkbook(fs);
        
        return workbook;
	}
	
	/**
	 * 一维Excel
	 * @param objList
	 * @param list
	 * @throws IOException
	 * @throws NumberFormatException
	 * @throws IllegalArgumentException
	 * @throws SecurityException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 * @throws NoSuchMethodException
	 */
	public static HSSFWorkbook createExcelList(List objList, List list, HSSFWorkbook workbook, String condation) throws IOException, NumberFormatException, IllegalArgumentException, SecurityException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
		// xml文件配置字段数 list.size() - 2，从0开始计数，循环变量为list.size() - 1;
		int count = list.size() - 1;
		// 对象数组含有数据个数
		int rowIndexCount = objList.size();

		// 先读取模板 
       // POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(list.get(0).toString())); 
        // 基于模板创建workbook 
       // HSSFWorkbook workbook = new HSSFWorkbook(fs);
        // 如果模板存在多页的话可以分别取到 
        HSSFSheet sheet = workbook.getSheetAt(Integer.parseInt(list.get(1).toString())); 
        // 模板开始填充的第一行
        List tmpStart = (List) list.get(2);
		int rowStart = Integer.parseInt(tmpStart.get(1).toString().trim());
		
        // 模板最后行数
        int rowCount = sheet.getLastRowNum();
        // 插入开始行数 第二行
        int rowInsertStart = rowStart + 1;
        // 获得第一行
        HSSFRow rowFirst = sheet.getRow(rowStart);
        // 获得列数 
        int colNum = rowFirst.getLastCellNum();
        List style = new ArrayList();
        for (int i = 0; i < colNum; i++) {
        	style.add(rowFirst.getCell(i).getCellStyle());
        }
        // 生成行数
        int newCount = rowIndexCount - 1;
        // 生成行
        sheet.shiftRows(rowInsertStart, rowCount + newCount, newCount, true, false); 
		for (int i = 0; i < newCount; i++) {
			HSSFRow rowNew = sheet.createRow(rowInsertStart);
			for (int j = 0; j < colNum; j++) {
				 // 行样式
				rowNew.createCell(j);
				rowNew.getCell(j).setCellStyle((CellStyle) style.get(j));
			}
			rowInsertStart++;
		}
        
		// 填充excel文件
		for (int i = 2; i <= count; i++) {
			List tmp = (List) list.get(i);
			int col = Integer.parseInt(tmp.get(0).toString().trim());
			int row = Integer.parseInt(tmp.get(1).toString().trim());
			String tag = tmp.get(2).toString().trim();
			String type = tmp.get(3).toString().trim();
			String rule = tmp.get(4).toString().trim();
			String restrict = tmp.get(5).toString().trim();
			
			if (tag != null && !tag.equals("")) {
				for (int rowIndex = 0; rowIndex < rowIndexCount; rowIndex++) {
					// 行 
					HSSFRow rowTmp = sheet.getRow(row + rowIndex); 
					// 单元格 
				    HSSFCell cell = rowTmp.getCell(col); 
					// 设置单元格的值 
				    cell.setCellValue(getMethod(objList.get(rowIndex), tag)); 
					
				}
				// 保存规则条件
				if (rule != null && !rule.equals("")) {
					for (int rowIndex = 0; rowIndex < rowIndexCount; rowIndex++) {
						putMap(rule + rowIndex, list, i, objList.get(rowIndex));
					}
				}
			} else {
				if (rule.equals(DATE)) { //没有标签 规则是DATE表明是插入日期
					// 行 
					HSSFRow rowTmp = sheet.getRow(row + newCount); 
					// 单元格 
					HSSFCell cell = rowTmp.getCell(col); 
					// 设置单元格的值 
					cell.setCellValue(getCurrentDate());
				} else if (rule.equals(CONDATION)) { //没有标签 规则是CONDATION表明是统计口径
					// 行 
					HSSFRow rowTmp = sheet.getRow(row + newCount); 
					// 单元格 
					HSSFCell cell = rowTmp.getCell(col); 
					// 设置单元格的值 
					cell.setCellValue(condation);
				} else { //没有标签 规则是其他 表明是计算
					// 行 
					HSSFRow rowTmp = sheet.getRow(row + rowIndexCount - 1); 
					// 单元格 
					HSSFCell cell = rowTmp.getCell(col); 
					// 设置单元格的值 
					cell.setCellValue(calcRule(rule, list, objList));
				}
			}
		}
        // 输出Excel 
      /*  FileOutputStream stream = new  FileOutputStream(excelFilePath); 
        workbook.write(stream); 
        
        stream.flush(); 
        stream.close();
        
        // 输出流 
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        baos.flush();
        byte[] aa = baos.toByteArray();
        InputStream is = new ByteArrayInputStream(aa,0,aa.length);
        baos.close();*/
        
        return workbook;
	}

	/**
	 * 二维Excel
	 * @param obj
	 * @param list
	 * @throws IOException
	 * @throws NumberFormatException
	 * @throws IllegalArgumentException
	 * @throws SecurityException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 * @throws NoSuchMethodException
	 */
	public static HSSFWorkbook createExcel(Object obj, List list, HSSFWorkbook workbook)
			throws IOException, NumberFormatException,
			IllegalArgumentException, SecurityException,
			IllegalAccessException, InvocationTargetException,
			NoSuchMethodException {
		// 配置文件中字段个数list.size() - 2，从0开始计数，循环变量为list.size() - 1;
		int count = list.size() - 1;
		// 先读取模板 
       // POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(list.get(0).toString())); 
        // 基于模板创建workbook 
       // HSSFWorkbook workbook = new HSSFWorkbook(fs);
        // 模板页
        HSSFSheet sheet = workbook.getSheetAt(Integer.parseInt(list.get(1).toString())); 
		
		// 填充excel文件
		for (int i = 2; i <= count; i++) {
			List tmp = (List) list.get(i);
			int col = Integer.parseInt(tmp.get(0).toString().trim());
			int row = Integer.parseInt(tmp.get(1).toString().trim());
			String tag = tmp.get(2).toString().trim();
			String type = tmp.get(3).toString().trim();
			String rule = tmp.get(4).toString().trim();
			String restrict = tmp.get(5).toString().trim();
			if (tag != null && !tag.equals("")) {
				// 行 
			    HSSFRow rowTmp = sheet.getRow(row); 
			    // 单元格 
			    HSSFCell cell = rowTmp.getCell(col); 
			    // 设置单元格的值 
			    cell.setCellValue(getMethod(obj, tag)); 
			    // 保存规则条件
				if (rule != null && !rule.equals("")) {
					putMap(rule, list, i, obj);
				}
			} else { //没有标签表明是求和的字段
				if (rule != null && !rule.equals("")) {
					// 行 
				    HSSFRow rowTmp = sheet.getRow(row); 
				    // 单元格 
				    HSSFCell cell = rowTmp.getCell(col); 
				    // 设置单元格的值 
				    cell.setCellValue(calcRule(rule, list, obj)); 
				}
			}
		}
		/*// 输出Excel 
        FileOutputStream stream = new  FileOutputStream(excelFilePath); 
        workbook.write(stream); 
        
        stream.flush(); 
        stream.close();
        
        // 输出流 
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        baos.flush();
        byte[] aa = baos.toByteArray();
        InputStream is = new ByteArrayInputStream(aa,0,aa.length);
        baos.close();*/
        return workbook;
	}

	/**
	 * 根据配置的规则计算数值型结果
	 * 
	 * @param rule
	 * @param list
	 * @param index
	 * @param obj
	 * @return
	 */
	public static double calcRule(String rule, List list, Object obj) {
		List objList = new ArrayList();
		if (obj instanceof List) {
			objList = (List) obj;
		}
		
		int count = objList.size();
		int length = rule.length();
		double res = 0;
		BigDecimal big = new BigDecimal(0);
		
		if (length != 1) {
				if (rule.substring(0, 1).equals(ADD)) {
					BigDecimal bigdec[] = new BigDecimal[length];
					if (count == 0) {
						String s = rule.substring(1, rule.length());
						String st [] = rule.substring(1, rule.length()).split(DOT);
						for (int i = 0; i < st.length; i++) {
							if (ruleMap.get(st[i]).toString() == null || ruleMap.get(st[i]).toString().equals("")) {
								bigdec[i] = new BigDecimal(0);
								big = big.add(bigdec[i]);
								res = big.doubleValue();
							} else {
								bigdec[i] = new BigDecimal(Double.parseDouble((String) ruleMap.get(st[i])));
								big = big.add(bigdec[i]);
								res = big.doubleValue();
							}
						}
					} else {
						for (int i = 1; i < length; i++) {
							for (int j = 0; j < count; j++) {
								if (ruleMap.get(rule.charAt(i) + "" + j).toString() == null ||  ruleMap.get(rule.charAt(i) + "" + j).toString().equals("")) {
									bigdec[i] = new BigDecimal(0);
									big = big.add(bigdec[i]);
									res = big.doubleValue();
								} else {
									bigdec[i] = new BigDecimal(Double.parseDouble((String) ruleMap.get(rule.charAt(i) + "" + j)));
									big = big.add(bigdec[i]);
									res = big.doubleValue();
								}
								
							}
						}
					}
				}
		}
		return res;
	}

	/**
	 * 根据配置规则保存计算条件
	 * 
	 * @param rule
	 * @param list
	 * @param index
	 * @param obj
	 * @throws IllegalArgumentException
	 * @throws SecurityException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 * @throws NoSuchMethodException
	 */
	public static void putMap(String rule, List list, int index, Object obj)
			throws IllegalArgumentException, SecurityException,
			IllegalAccessException, InvocationTargetException,
			NoSuchMethodException {
		List tmp = (List) list.get(index);
		ruleMap.put(rule, getMethod(obj, tmp.get(2).toString()));
	}

	/**
	 * 反射，根据参数名称调用对应的get方法，获得对象对应字段属性值
	 * 
	 * @param obj
	 * @param str
	 * @return
	 * @throws IllegalArgumentException
	 * @throws SecurityException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 * @throws NoSuchMethodException
	 */
	public static String getMethod(Object obj, String str)
			throws IllegalArgumentException, SecurityException,
			IllegalAccessException, InvocationTargetException,
			NoSuchMethodException {
		String method = "get" + str;
		Method m = obj.getClass().getMethod(method, new Class[] {});
		String result = (String) m.invoke(obj, null);

		return result;
	}

	/**
	 * 动态设置对象属性值
	 * 
	 * @param obj
	 * @param str
	 * @param value
	 * @throws IllegalArgumentException
	 * @throws SecurityException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 * @throws NoSuchMethodException
	 */
	public static void setMethod(Object obj, String str, String value)
			throws IllegalArgumentException, SecurityException,
			IllegalAccessException, InvocationTargetException,
			NoSuchMethodException {
		String method = "set" + str;
		Method m = obj.getClass().getMethod(method, new Class[] { String.class });
		m.invoke(obj, value);
	}

}
